package com.doublefish.doublefishrobot.utils.db.database;

import android.content.Context;
import android.util.Log;

import com.doublefish.doublefishrobot.utils.db.database.bean.SQLiteResult;
import com.doublefish.doublefishrobot.utils.db.database.bean.SqlInfoModel;

import net.sqlcipher.Cursor;
import net.sqlcipher.SQLException;
import net.sqlcipher.database.SQLiteDatabase;
import net.sqlcipher.database.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 作者:dugaofeng on 2024/10/16 14:32
 */
public class SQLiteDatabaseHelper extends SQLiteOpenHelper {

    private String mDbPwd;
    private int mVersion;
    private List<SqlInfoModel> mSqlInfoModels;

    public SQLiteDatabaseHelper(Context context, String name, String dbPwd, int version, List<SqlInfoModel> sqlInfoModels) {
        super(context,name,null,version);

        this.mDbPwd = dbPwd;
        this.mVersion = version;
        this.mSqlInfoModels = sqlInfoModels;

        //创建 更新表结构
        getWritableDatabase(dbPwd);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        if (mSqlInfoModels == null || mSqlInfoModels.size() == 0)
            return;

        //第一次创建表 只有第一次会执行，其他升级都执行onUpgrade
        sqLiteDatabase.beginTransaction();
        try {
            for (SqlInfoModel sqlInfoModel:mSqlInfoModels) {
                if (sqlInfoModel.getVersion() <= mVersion) {
                    //执行语句
                    SQLiteResult result = execSql(sqLiteDatabase,sqlInfoModel.getSqlString());
                    if (!result.isSuccess())
                        return;
                }
            }
            sqLiteDatabase.setTransactionSuccessful();
        } catch (Exception e) {
            Log.e("SQLiteDatabaseHelper","creat_tables " + e.getMessage());
        } finally {
            sqLiteDatabase.endTransaction();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        if (mSqlInfoModels == null || mSqlInfoModels.size() == 0)
            return;

        //执行升级语句 包括后续版本的新增 删除等语句
        sqLiteDatabase.beginTransaction();
        try {
            for (SqlInfoModel sqlInfoModel:mSqlInfoModels) {
                if (sqlInfoModel.getVersion() > oldVersion && sqlInfoModel.getVersion() <= mVersion) {
                    //执行语句
                    SQLiteResult result = execSql(sqLiteDatabase,sqlInfoModel.getSqlString());
                    if (!result.isSuccess())
                        return;
                }
            }
            sqLiteDatabase.setTransactionSuccessful();
        } catch (Exception e) {
            Log.e("SQLiteDatabaseHelper","onUpgrade "+ e.getMessage());
        } finally {
            sqLiteDatabase.endTransaction();
        }
    }

    //用于 onCreate  onUpgrade 执行sql
    private  SQLiteResult execSql(SQLiteDatabase sqLiteDatabase,String sql) {
        if (sqLiteDatabase == null)
            return SQLiteResult.SqLiteDatabaseIsNull();

        try {
            sqLiteDatabase.execSQL(sql);
            return SQLiteResult.SqlExecuteSuccess();
        } catch (SQLException ex) {
            Log.e("SQLiteDatabaseHelper", "execSql " + ex.getMessage());
            return  new SQLiteResult(false,ex.getMessage());
        }
    }


    //执行数据库语句 （除查找语句之外）
    public SQLiteResult execSql(String sql) {
        SQLiteDatabase sqLiteDatabase = getWritableDatabase(mDbPwd);
        if (sqLiteDatabase == null)
            return SQLiteResult.SqLiteDatabaseIsNull();

        try {
            sqLiteDatabase.execSQL(sql);
            return SQLiteResult.SqlExecuteSuccess();
        } catch (SQLException ex) {
            Log.e("SQLiteDatabaseHelper", "execSql " + ex.getMessage());
            return  new SQLiteResult(false,ex.getMessage());
        }
    }

    /**
     * 执行sql语句查询 count
     * @param sql  sql语句
     * @return
     */
    public SQLiteResult queryCount(String sql) {
        SQLiteDatabase sqLiteDatabase = getReadableDatabase(mDbPwd);
        if (sqLiteDatabase == null)
            return SQLiteResult.SqLiteDatabaseIsNull();

        Cursor cursor = null;
        try {
            cursor = sqLiteDatabase.rawQuery(sql, null);
            if (cursor == null)
                return SQLiteResult.SqLiteDatabaseCursorIsNull();

            int count = 0;
            if (cursor.moveToNext())
                count = cursor.getInt(0);

            cursor.close();
            return new SQLiteResult(true, count);
        } catch (Exception e) {
            Log.e("SQLiteDatabaseHelper", "queryCount " + e.getMessage());
            return new SQLiteResult(false,e.getMessage());
        } finally {
            if (cursor != null && !cursor.isClosed())
                cursor.close();
        }
    }

    /**
     * 查找数据
     * @param sql 查找 的sql
     * @return
     */
    public SQLiteResult queryData(String sql) {
        SQLiteDatabase sqLiteDatabase = getReadableDatabase(mDbPwd);
        if (sqLiteDatabase == null)
            return SQLiteResult.SqLiteDatabaseIsNull();

        Cursor cursor = null;
        try {
            cursor = sqLiteDatabase.rawQuery(sql, null);
            if (cursor == null)
                return SQLiteResult.SqLiteDatabaseCursorIsNull();

            List<Map<String, Object>> list = new ArrayList<>();
            int nColumnCounts = cursor.getColumnCount();
            while (cursor.moveToNext()) {
                Map<String, Object> item = makeQueryDataItem(cursor, nColumnCounts);
                list.add(item);
            }
            cursor.close();

            if (list.size() > 0) {
                return new SQLiteResult(true,list.get(0),list);
            } else {
                return new SQLiteResult(true);
            }
        } catch (Exception e) {
            Log.e("SQLiteDatabaseHelper","queryCount " + e.getMessage());
            return new SQLiteResult(false,e.getMessage());
        } finally {
            if (cursor != null && !cursor.isClosed())
                cursor.close();
        }
    }

    private Map<String,Object> makeQueryDataItem(Cursor cursor, int nColumnCounts) {
        Map<String,Object> map = new HashMap<>();
        for (int i = 0; i < nColumnCounts; i++) {
            String sFieldName = cursor.getColumnName(i);
            int nType = cursor.getType(i);
            if (nType == Cursor.FIELD_TYPE_NULL) {
                map.put(sFieldName, null);
            } else if (nType == Cursor.FIELD_TYPE_INTEGER) {
                map.put(sFieldName, cursor.getLong(i));
            } else if (nType == Cursor.FIELD_TYPE_FLOAT) {
                map.put(sFieldName, cursor.getDouble(i));
            } else if (nType == Cursor.FIELD_TYPE_STRING) {
                map.put(sFieldName, cursor.getString(i));
            } else if (nType == Cursor.FIELD_TYPE_BLOB) {
                map.put(sFieldName, cursor.getBlob(i));
            }
        }
        return map;
    }



    /******** 事物 **********/
    public void beginTransaction(){
        SQLiteDatabase sqLiteDatabase = getWritableDatabase(mDbPwd);
        sqLiteDatabase.beginTransaction();
    }

    public void setTransactionSuccessful() {
        SQLiteDatabase sqLiteDatabase = getWritableDatabase(mDbPwd);
        sqLiteDatabase.setTransactionSuccessful();
    }

    public void endTransaction() {
        SQLiteDatabase sqLiteDatabase = getWritableDatabase(mDbPwd);
        sqLiteDatabase.endTransaction();
    }



    /**
     * 判断指定的表是否存在
     *
     * @param tableName 要查找的表名
     * @return true表示存在，false表示不存在
     */
    public boolean isExistTable(String tableName) {
        if (tableName == null || tableName.equals("")) {
            return false;
        }

        Cursor cursor = null;
        boolean exist = false;
        try {
            SQLiteDatabase database = getReadableDatabase(mDbPwd);
            cursor = database.rawQuery("select count(*) from sqlite_master where type='table' and name = ?",
                    new String[]{tableName});
            if (cursor != null && cursor.moveToNext()) {
                int counts = cursor.getInt(0);
                if (counts > 0) {
                    exist = true;
                }
                cursor.close();
            }
        } catch (Exception ex) {
            Log.e("SQLiteDatabaseHelper","isExistTable " + ex.getMessage());
        } finally {
            if (null != cursor && !cursor.isClosed()) {
                cursor.close();
            }
        }

        return exist;
    }

    /**
     * 获取指定表的字段名和字段信息
     *
     * @param tableName 表名称
     * @return 表对应的字段 和 类型
     */
    public Map<String,Integer> getFieldsOfTable(String tableName) {
        Cursor cursor = null;
        Map<String,Integer> fieldsOfTable = new HashMap();

        try {
            SQLiteDatabase database = getReadableDatabase(mDbPwd);
            if (database == null)
                return null;

            cursor = database.rawQuery(String.format("PRAGMA table_info(%s);", tableName), null);
            while (cursor.moveToNext()) {
                int nameIndex = cursor.getColumnIndex("name");
                String fieldName = cursor.getString(nameIndex);
                int typeIndex = cursor.getColumnIndex("type");
                String type = cursor.getString(typeIndex).toUpperCase();

                switch (type) {
                    case "NULL":
                        fieldsOfTable.put(fieldName, Cursor.FIELD_TYPE_NULL);
                        break;
                    case "INTEGER":
                        fieldsOfTable.put(fieldName, Cursor.FIELD_TYPE_INTEGER);
                        break;
                    case "REAL":
                        fieldsOfTable.put(fieldName, Cursor.FIELD_TYPE_FLOAT);
                        break;
                    case "TEXT":
                        fieldsOfTable.put(fieldName, Cursor.FIELD_TYPE_STRING);
                        break;
                    case "BLOB":
                        fieldsOfTable.put(fieldName, Cursor.FIELD_TYPE_BLOB);
                        break;

                    default:
                        fieldsOfTable.put(fieldName, Cursor.FIELD_TYPE_STRING);
                        break;
                }
            }
            cursor.close();
        } catch (Exception ex) {
            Log.e("SQLiteDatabaseHelper","getFieldsOfTable " + ex.getMessage());
        } finally {
            if (cursor != null && !cursor.isClosed()) {
                cursor.close();
            }
        }
        return fieldsOfTable;
    }
}
